import type { FRange, FWorksheet } from '@univerjs/preset-sheets-core'
import { type FUniver, type Nullable, BorderStyleTypes } from '@univerjs/presets'
import * as XLSX from 'xlsx-js-style'
import type { CellObject, CellStyle } from 'xlsx-js-style'

export const useCmpSheetsUseExport = () => {
  /**
   * 导出当前工作簿为 Excel 文件
   * @param fileName 文件名（不含扩展名）
   */
  const exportToExcel = async (fUniver: FUniver, fileName: string) => {
    exportTest(fUniver, fileName)
    // export1Test()
    // export2Test()
  }

  // 项目中的导出
  const exportTest = (fUniver: FUniver, fileName: string) => {
    const workbook = fUniver.getActiveWorkbook()
    const wb: XLSX.WorkBook = XLSX.utils.book_new()
    // 遍历所有工作表
    workbook?.getSheets().forEach((sheet) => {
      const ws = convertUniverSheetToXLSX(sheet)
      XLSX.utils.book_append_sheet(wb, ws, sheet.getSheetName())
    })
    XLSX.writeFile(wb, `${fileName}.xlsx`)
  }

  // 单元格合并 并设置 border 颜色
  const export1Test = () => {
    const worksheet: XLSX.WorkSheet = {}
    // 创建工作表和工作簿
    const workbook = XLSX.utils.book_new()

    // 合并单元格（A1 到 B2）
    worksheet['!merges'] = [
      {
        s: { r: 0, c: 0 },
        e: { r: 1, c: 1 },
      },
    ]

    worksheet['!ref'] = XLSX.utils.encode_range({
      s: { r: 0, c: 0 },
      e: { r: 20, c: 20 },
    })

    // 定义边框样式（红色细边框）
    const borderStyle = {
      top: { style: 'thin', color: { rgb: '000000' } },
      bottom: { style: 'thin', color: { rgb: '000000' } },
      left: { style: 'thin', color: { rgb: '000000' } },
      right: { style: 'thin', color: { rgb: '000000' } },
    }

    // bgColor: { // 背景色
    //   rgb: "",
    //   theme: 0, // 主题颜色索引
    //   tint: 0, // 按百分比着色
    // }

    // 应用样式到起始单元格（A1）
    worksheet['A1'] = {
      t: 's',
      v: '合并单元格内容',
      s: {
        fill: { bgColor: { rgb: 'fff4b9' }, fgColor: { rgb: '84e1bc' } },
        border: {
          left: { style: 'thin', color: { rgb: '000000' } },
          top: { style: 'thin', color: { rgb: '000000' } },
        },
      },
    }

    // 隐藏内部边框（例如 B1、A2、B2）
    worksheet['B1'] = {
      t: 's',
      v: '',
      s: {
        border: {
          top: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } },
        },
      },
    }
    worksheet['A2'] = {
      t: 's',
      v: '',
      s: {
        border: {
          bottom: { style: 'thin', color: { rgb: '000000' } },
          left: { style: 'thin', color: { rgb: '000000' } },
        },
      },
    }
    worksheet['B2'] = {
      t: 's',
      v: '',
      s: {
        border: {
          bottom: { style: 'thin', color: { rgb: '000000' } },
          right: { style: 'thin', color: { rgb: '000000' } },
        },
      },
    }

    // 应用样式到起始单元格（A1）
    worksheet['A4'] = {
      v: '所属科室',
      t: 's',
      s: {
        fill: {
          bgColor: {
            rgb: 'ffffff',
          },
          fgColor: {
            rgb: 'ffffff',
          },
        },
        font: {
          sz: 11,
          color: {},
          bold: false,
        },
        alignment: {
          vertical: 'general',
          horizontal: 'general',
        },
        border: {
          top: {
            style: 'thin',
            color: {
              rgb: '000000',
            },
          },
          right: {
            style: 'thin',
            color: {
              rgb: '000000',
            },
          },
          bottom: {
            style: 'thin',
            color: {
              rgb: '000000',
            },
          },
          left: {
            style: 'thin',
            color: {
              rgb: '000000',
            },
          },
        },
      },
    }
    console.log(`output->worksheet`, worksheet)

    XLSX.utils.book_append_sheet(workbook, worksheet, 'readme demo')
    // 保存文件
    XLSX.writeFile(workbook, 'output.xlsx')
  }

  // 官网 案例
  const export2Test = () => {
    // STEP 1: Create a new workbook
    const wb = XLSX.utils.book_new()

    // STEP 2: Create data rows and styles
    const row = [
      { v: 'Courier: 24', t: 's', s: { font: { name: 'Courier', sz: 24 } } },
      { v: 'bold & color', t: 's', s: { font: { bold: true, color: { rgb: 'FF0000' } } } },
      { v: 'fill: color', t: 's', s: { fill: { fgColor: { rgb: 'E9E9E9' } } } },
      { v: 'line\nbreak', t: 's', s: { alignment: { wrapText: true } } },
    ]

    // STEP 3: Create worksheet with rows; Add worksheet to workbook
    const ws = XLSX.utils.aoa_to_sheet([row])
    XLSX.utils.book_append_sheet(wb, ws, 'readme demo')

    // STEP 4: Write Excel file to browser
    XLSX.writeFile(wb, 'xlsx-js-style-demo.xlsx')
  }

  //#region univer => xlsx-js-style 格式转换

  /**
   * 将 Univer Worksheet 转换为 xlsx-js-style 格式
   */
  const convertUniverSheetToXLSX = (sheet: FWorksheet): XLSX.WorkSheet => {
    const ws: XLSX.WorkSheet = {}
    const maxRows = sheet.getMaxRows()
    const maxCols = sheet.getMaxColumns()

    // 1.处理单元格数据
    for (let r = 0; r < maxRows; r++) {
      for (let c = 0; c < maxCols; c++) {
        const cell = sheet.getRange(r, c)
        // 后续: 需要进行一系列的判断, 判断该单元格是否有导出价值
        // 暂时仅判断 值是否存在
        // cell.getValue()
        // cell.getCellStyle()

        // console.log(r, c, cell?.getValue(), cell.getCellStyle())

        if (
          !cell?.getValue() &&
          cell?.getValue() != false &&
          JSON.stringify(cell.getCellStyleData()) == '{}'
        )
          continue

        const cellAddress = XLSX.utils.encode_cell({ r, c })

        ws[cellAddress] = convertUniverCellToXLSX(cell)
      }
    }

    // 2. 设置列宽（关键新增部分）
    // 设置行高（关键新增部分）(excel 中的行高展示很奇怪暂不展示)
    ws['!cols'] = []
    for (let c = 0; c < maxCols; c++) {
      const width = sheet.getColumnWidth(c)
      // Univer 列宽单位转换为 Excel 的字符宽度（假设 1单位 ≈ 1/7 个字符宽度）
      ws['!cols'].push({ wch: Math.round(width / 7) || 10 })
    }

    // ws['!rows'] = []
    // for (let r = 0; r < maxRows; r++) {
    //   const height = sheet.getRowHeight(r)
    //   // Univer 行高单位转换为 Excel 的点数（假设 1单位 ≈ 1点）
    //   ws['!rows'].push({ hpt: height || 15 })
    // }

    // 3. 处理合并单元格（关键新增部分）
    // console.log('mergeData', sheet.getMergedRanges())
    const merges = sheet.getMergedRanges()
    if (merges && merges.length > 0) {
      ws['!merges'] = merges.map((merge: FRange) => ({
        s: { r: merge.getRow(), c: merge.getColumn() },
        e: { r: merge.getLastRow(), c: merge.getLastColumn() },
      }))
    }

    // 4.设置工作表范围
    ws['!ref'] = XLSX.utils.encode_range({
      s: { r: 0, c: 0 },
      e: { r: maxRows - 1, c: maxCols - 1 },
    })

    console.log(`output->ws`, ws)

    return ws
  }

  /**
   * 转换单个单元格数据
   */
  const convertUniverCellToXLSX = (cell: FRange): CellObject => {
    // 解析值
    let cellValue: CellObject['v'] = cell.getValue() ?? ''
    let cellType: CellObject['t'] = 's'

    // 类型推断
    if (typeof cellValue === 'number') {
      cellType = 'n'
      if (isExcelDate(cellValue)) {
        cellType = 'd'
        cellValue = new Date((cellValue - 25569) * 86400 * 1000)
      }
    } else if (typeof cellValue === 'boolean') {
      cellType = 'b'
    }

    // 解析样式（需根据 Univer 实际样式结构调整）
    const style: CellStyle = {
      // fill: { bgColor: { rgb: cell.getBackground()?.replace('#', '') ?? 'ffffff' } },
      fill: getCellFill(cell),
      font: getCellFont(cell),
      alignment: getCellAlignment(cell),
      border: getCellBorderStyle(cell),
    }

    const excelCell: CellObject = {
      v: cellValue!,
      t: cellType,
      s: style,
    }

    // 日期格式
    if (cellType === 'd') {
      excelCell.z = 'yyyy-mm-dd hh:mm:ss'
    }

    return excelCell
  }

  //#endregion

  //#region 通用函数

  /**
   * 检查是否为 Excel 日期值
   */
  const isExcelDate = (value: number): boolean => {
    return value > 25569 && value < 2958465 // 1900-01-01 到 9999-12-31
  }

  //#endregion

  //#region cell 样式获取与转换

  /**
   * 字体设置
   * @param cell
   * @returns
   */
  const getCellFill = (cell: FRange): CellStyle['fill'] => {
    // console.log("export cell style", cell.getValue(), cell.getCellStyleData());
    const colorCNVT = (v: Nullable<string>) => {
      if (!v) return 'FFFFFF'
      const index = v.indexOf('#')
      if (index > -1) return v.substring(index + 1)
      return rgbStringToHex(v, false)
    }

    return {
      // bgColor: { // 背景色
      //   rgb: "",
      //   theme: 0, // 主题颜色索引
      //   tint: 0, // 按百分比着色
      // },
      fgColor: {
        // 前景色
        rgb: colorCNVT(cell.getCellStyle()?.background?.rgb),
      },
      // patternType: "solid",
    }
  }

  /**
   * 字体设置
   * @param cell
   * @returns
   */
  const getCellFont = (cell: FRange): CellStyle['font'] => {
    const colorCNVT = (v: Nullable<string>) => {
      if (!v) return undefined
      const index = v.indexOf('#')
      if (index > -1) return v.substring(index + 1)
      return undefined
    }
    return {
      sz: cell.getFontSize() ?? 11, // 默认值
      color: { rgb: colorCNVT(cell.getCellStyle()?.color?.rgb) },
      bold: cell.getCellStyle()?.bold,
    }
  }

  /**
   * 值位置
   * @param cell
   * @returns
   */
  const getCellAlignment = (cell: FRange): CellStyle['alignment'] => {
    // // general 为默认值
    // const vertical = cell.getVerticalAlignment() as 'top' | 'center' | 'bottom' | 'general'
    // const horizontal = cell.getHorizontalAlignment() as 'left' | 'center' | 'right' | 'general'
    // return {
    //   vertical: vertical === 'general' ? 'bottom' : vertical,
    //   horizontal: horizontal === 'general' ? 'left' : horizontal,
    // }

    // general  为默认值
    // 字符串    默认在左边
    // 数字      默认在右边
    const vertical = cell.getVerticalAlignment() as 'top' | 'center' | 'bottom'
    const horizontal = cell.getHorizontalAlignment() as 'left' | 'center' | 'right'
    return {
      vertical: vertical,
      horizontal: horizontal,
    }
  }

  /**
   * 获取默认边框样式
   */
  const getCellBorderStyle = (cell: FRange): CellStyle['border'] => {
    const styleCNVT = (v: BorderStyleTypes | undefined) => {
      if (!v) return 'thin'
      switch (v as BorderStyleTypes) {
        case BorderStyleTypes.NONE:
          return 'thin'
        case BorderStyleTypes.THIN:
          return 'thin'
        case BorderStyleTypes.HAIR:
          return 'hair'
        case BorderStyleTypes.DOTTED:
          return 'dotted'
        case BorderStyleTypes.DASHED:
          return 'dashed'
        case BorderStyleTypes.DASH_DOT:
          return 'dashDot'
        case BorderStyleTypes.DASH_DOT_DOT:
          return 'dashDotDot'
        case BorderStyleTypes.DOUBLE:
          return undefined
        case BorderStyleTypes.MEDIUM:
          return 'medium'
        case BorderStyleTypes.MEDIUM_DASHED:
          return 'mediumDashed'
        case BorderStyleTypes.MEDIUM_DASH_DOT:
          return 'mediumDashDot'
        case BorderStyleTypes.MEDIUM_DASH_DOT_DOT:
          return 'mediumDashDotDot'
        case BorderStyleTypes.SLANT_DASH_DOT:
          return 'slantDashDot'
        case BorderStyleTypes.THICK:
          return 'thick'

        default:
          return undefined
      }
    }
    const colorCNVT = (v: Nullable<string>) => {
      if (!v) return 'd4d4d4'
      const index = v.indexOf('#')
      if (index > -1) return v.substring(index + 1)
      return rgbStringToHex(v, false)
    }

    const border = cell.getCellStyle()?.border
    const t = border?.t
    const r = border?.r
    const b = border?.b
    const l = border?.l

    return {
      top: { style: styleCNVT(t?.s), color: { rgb: colorCNVT(t?.cl.rgb) } },
      right: { style: styleCNVT(r?.s), color: { rgb: colorCNVT(r?.cl.rgb) } },
      bottom: { style: styleCNVT(b?.s), color: { rgb: colorCNVT(b?.cl.rgb) } },
      left: { style: styleCNVT(l?.s), color: { rgb: colorCNVT(l?.cl.rgb) } },
    }
  }

  //#endregion

  //#region 工具函数

  // 颜色转换字典

  const colorMap: Record<string, string> = {
    aliceblue: 'f0f8ff',
    antiquewhite: 'faebd7',
    aqua: '00ffff',
    aquamarine: '7fffd4',
    azure: 'f0ffff',
    beige: 'f5f5dc',
    bisque: 'ffe4c4',
    black: '000000',
    blanchedalmond: 'ffebcd',
    blue: '0000ff',
    blueviolet: '8a2be2',
    brown: 'a52a2a',
    burlywood: 'deb887',
    cadetblue: '5f9ea0',
    chartreuse: '7fff00',
    chocolate: 'd2691e',
    coral: 'ff7f50',
    cornflowerblue: '6495ed',
    cornsilk: 'fff8dc',
    crimson: 'dc143c',
    cyan: '00ffff',
    darkblue: '00008b',
    darkcyan: '008b8b',
    darkgoldenrod: 'b8860b',
    darkgray: 'a9a9a9',
    darkgreen: '006400',
    darkkhaki: 'bdb76b',
    darkmagenta: '8b008b',
    darkolivegreen: '556b2f',
    darkorange: 'ff8c00',
    darkorchid: '9932cc',
    darkred: '8b0000',
    darksalmon: 'e9967a',
    darkseagreen: '8fbc8f',
    darkslateblue: '483d8b',
    darkslategray: '2f4f4f',
    darkturquoise: '00ced1',
    darkviolet: '9400d3',
    deeppink: 'ff1493',
    deepskyblue: '00bfff',
    dimgray: '696969',
    dodgerblue: '1e90ff',
    firebrick: 'b22222',
    floralwhite: 'fffaf0',
    forestgreen: '228b22',
    fuchsia: 'ff00ff',
    gainsboro: 'dcdcdc',
    ghostwhite: 'f8f8ff',
    gold: 'ffd700',
    goldenrod: 'daa520',
    gray: '808080',
    green: '008000',
    greenyellow: 'adff2f',
    honeydew: 'f0fff0',
    hotpink: 'ff69b4',
    indianred: 'cd5c5c',
    indigo: '4b0082',
    ivory: 'fffff0',
    khaki: 'f0e68c',
    lavender: 'e6e6fa',
    lavenderblush: 'fff0f5',
    lawngreen: '7cfc00',
    lemonchiffon: 'fffacd',
    lightblue: 'add8e6',
    lightcoral: 'f08080',
    lightcyan: 'e0ffff',
    lightgoldenrodyellow: 'fafad2',
    lightgray: 'd3d3d3',
    lightgreen: '90ee90',
    lightpink: 'ffb6c1',
    lightsalmon: 'ffa07a',
    lightseagreen: '20b2aa',
    lightskyblue: '87cefa',
    lightslategray: '778899',
    lightsteelblue: 'b0c4de',
    lightyellow: 'ffffe0',
    lime: '00ff00',
    limegreen: '32cd32',
    linen: 'faf0e6',
    magenta: 'ff00ff',
    maroon: '800000',
    mediumaquamarine: '66cdaa',
    mediumblue: '0000cd',
    mediumorchid: 'ba55d3',
    mediumpurple: '9370db',
    mediumseagreen: '3cb371',
    mediumslateblue: '7b68ee',
    mediumspringgreen: '00fa9a',
    mediumturquoise: '48d1cc',
    mediumvioletred: 'c71585',
    midnightblue: '191970',
    mintcream: 'f5fffa',
    mistyrose: 'ffe4e1',
    moccasin: 'ffe4b5',
    navajowhite: 'ffdead',
    navy: '000080',
    oldlace: 'fdf5e6',
    olive: '808000',
    olivedrab: '6b8e23',
    orange: 'ffa500',
    orangered: 'ff4500',
    orchid: 'da70d6',
    palegoldenrod: 'eee8aa',
    palegreen: '98fb98',
    paleturquoise: 'afeeee',
    palevioletred: 'db7093',
    papayawhip: 'ffefd5',
    peachpuff: 'ffdab9',
    peru: 'cd853f',
    pink: 'ffc0cb',
    plum: 'dda0dd',
    powderblue: 'b0e0e6',
    purple: '800080',
    rebeccapurple: '663399',
    red: 'ff0000',
    rosybrown: 'bc8f8f',
    royalblue: '4169e1',
    saddlebrown: '8b4513',
    salmon: 'fa8072',
    sandybrown: 'f4a460',
    seagreen: '2e8b57',
    seashell: 'fff5ee',
    sienna: 'a0522d',
    silver: 'c0c0c0',
    skyblue: '87ceeb',
    slateblue: '6a5acd',
    slategray: '708090',
    snow: 'fffafa',
    springgreen: '00ff7f',
    steelblue: '4682b4',
    tan: 'd2b48c',
    teal: '008080',
    thistle: 'd8bfd8',
    tomato: 'ff6347',
    turquoise: '40e0d0',
    violet: 'ee82ee',
    wheat: 'f5deb3',
    white: 'ffffff',
    whitesmoke: 'f5f5f5',
    windowtext: '000000', // 后续: 不确定是从哪获取/设置的颜色, 检查是哪里获取到的 窗口文本颜色
    yellow: 'ffff00',
    yellowgreen: '9acd32',
  }

  /**
   * 将颜色名称转换为 16 进制颜色值
   * @param {string} colorName - 颜色名称
   * @returns {string|null} - 返回以 # 开头的 16 进制颜色值，如果未找到则返回 null
   */
  function colorNameToHex(colorName: string) {
    const lowerCaseColor = colorName.trim().toLowerCase()
    return colorMap[lowerCaseColor] || null
  }

  /**
   * 将 RGB 字符串（如 "rgb(255, 99, 71)"）转换为 16 进制颜色值
   * @param {string} rgbString - RGB 字符串
   * @param {boolean} isCarrychart - 是否使用进位制（如 #FF6347），默认为 true
   * @returns {string} - 返回以 # 开头的 16 进制颜色值
   */
  function rgbStringToHex(rgbString: string, isCarrychart: boolean = true): string {
    if (Object.keys(colorMap).includes(rgbString.toLowerCase())) {
      const color = colorNameToHex(rgbString.toLowerCase())
      // 如果为空, 也可以再向 color 字典中增加属性即可
      if (color) return color
    }

    // 使用正则表达式提取 RGB 分量
    const match = rgbString.match(/^rgb\((\d+),\s*(\d+),\s*(\d+)\)$/i)
    if (!match) {
      throw new Error("Invalid RGB string format. Expected format: 'rgb(r, g, b)'")
    }

    // 提取红、绿、蓝分量
    const r = parseInt(match[1], 10)
    const g = parseInt(match[2], 10)
    const b = parseInt(match[3], 10)

    // 确保每个分量在 0-255 的范围内
    if (r < 0 || r > 255 || g < 0 || g > 255 || b < 0 || b > 255) {
      throw new Error('RGB values must be in the range 0-255.')
    }

    // 转换为 16 进制并补零
    const toHex = (value: number) => {
      const hex = value.toString(16) // 转换为 16 进制
      return hex.length === 1 ? '0' + hex : hex // 补零
    }

    // 拼接结果并返回
    return `${isCarrychart ? '#' : ''}${toHex(r)}${toHex(g)}${toHex(b)}`
  }

  //#endregion

  return { exportToExcel }
}
